package com.liziing.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.liziing.pojo.ClassroomUsage;
import org.apache.ibatis.annotations.Select;


import java.util.HashMap;
import java.util.List;

public interface ClassroomUsageMapper extends BaseMapper<ClassroomUsage> {
    //获取每个教室的使用率
    @Select("SELECT cu.classroom_id, cr.class_student_count, ci.classroom_name, ci.belonging_building, ci.capacity, ROUND((cr.class_student_count / ci.capacity * 100), 2) AS usage_rate\n" +
            "FROM (\n" +
            "    SELECT classroom_id, class_id\n" +
            "    FROM classroom_usage\n" +
            "    WHERE classroom_status = '使用中'\n" +
            "    GROUP BY classroom_id, class_id\n" +
            ") AS cu\n" +
            "JOIN classroom_information AS ci ON cu.classroom_id = ci.classroom_id\n" +
            "JOIN class_information AS cr ON cr.class_id = cu.class_id;\n")
    public List<HashMap<String,Object>> getClasRoomUsageRate();

    @Select("SELECT cu.id,ci.class_id,ci.class_name,cr.classroom_id,cr.classroom_name,co.course_id,co.course_name,us.id as uid,us.`name`,cu.classroom_status,cu.usage_time\n" +
            "FROM class_information ci,classroom_information cr,course_information co,users us,classroom_usage cu \n" +
            "WHERE \n" +
            "cu.class_id = ci.class_id and cu.classroom_id = cr.classroom_id and cu.course_id = co.course_id and cu.teacher_id = us.id ")
    public List<HashMap<String,Object>> getClasRoomUsageData();

}
